package com.oasystem.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.oasystem.domain.Operator;
import com.oasystem.domain.PagingBean;
import com.oasystem.domain.Permission;

public class OperatorDAO {
	private JdbcTemplate jdbcTemplate;

	public int insertOperator(final Operator operator) {
		KeyHolder key = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {

			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection
						.prepareStatement("insert into operator(name,username,password) values(?,?,?)");
				ps.setString(1, operator.getName());
				ps.setString(2, operator.getUserName());
				ps.setString(3, operator.getPassword());
				return ps;
			}
		}, key);
		return key.getKey().intValue();
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public List<Operator> getPagingOperators(PagingBean<Operator> pagingBean) {

		final int currentRecordIndex = pagingBean.getCurrentRecordIndex();
		final int pageSize = pagingBean.getPageSize();
		pagingBean.setAllRecordCount(jdbcTemplate
				.queryForInt("select count(id) from operator"));

		List<Operator> list = jdbcTemplate.query(
				new PreparedStatementCreator() {

					public PreparedStatement createPreparedStatement(
							Connection con) throws SQLException {
						PreparedStatement ps = con
								.prepareStatement("select id,name,userName from operator order by id desc limit ?,?");
						ps.setInt(1, currentRecordIndex);
						ps.setInt(2, pageSize);
						return ps;
					}
				}, new RowMapper<Operator>() {

					public Operator mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Operator operator = new Operator();
						operator.setId(rs.getInt("id"));
						operator.setName(rs.getString("name"));
						operator.setUserName(rs.getString("userName"));
						return operator;
					}
				});
		pagingBean.setData(list);
		return list;
	}

	public void load(Operator operator) {
		Operator result = jdbcTemplate.queryForObject(
				"select * from operator where id=?",
				new Object[] { operator.getId() }, new RowMapper<Operator>() {

					public Operator mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Operator operator = new Operator();
						operator.setId(rs.getInt("id"));
						operator.setName(rs.getString("name"));
						operator.setUserName(rs.getString("userName"));
						return operator;
					}
				});

		operator.copy(result);
	}

	public void updateOperator(final Operator operator) {
		String sql = null;
		if (operator.getPassword() == null) {
			sql = "update operator set name=?,userName=? where id=?";
		} else {
			sql = "update operator set name=?,userName=?,password=? where id=?";
		}
		jdbcTemplate.update(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				if (operator.getPassword() == null) {
					ps.setString(1, operator.getName());
					ps.setString(2, operator.getUserName());
					ps.setInt(3, operator.getId());

				} else {
					ps.setString(1, operator.getName());
					ps.setString(2, operator.getUserName());
					ps.setString(3, operator.getPassword());
					ps.setInt(4, operator.getId());
				}
			}
		});

	}

	public void deleteOperator(Operator Operator) {
		jdbcTemplate.update("delete from operator where id=?",
				new Object[] { Operator.getId() });
	}

	public List<Operator> getAllOperatorsWithoutPermission() {
		return jdbcTemplate.query(
				"select id,name,userName from operator order by id desc",
				new RowMapper<Operator>() {

					public Operator mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Operator operator = new Operator();
						operator.setId(rs.getInt("id"));
						operator.setName(rs.getString("name"));
						operator.setUserName(rs.getString("userName"));
						return operator;
					}
				});
	}

	public boolean isLegal(Operator operator) {
		List<Operator> list = jdbcTemplate
				.query(
						"select * from operator where username=? and password=?",
						new Object[] { operator.getUserName(),
								operator.getPassword() },
						new RowMapper<Operator>() {

							public Operator mapRow(ResultSet rs, int rowNum)
									throws SQLException {
								Operator op = new Operator();
								op.setName(rs.getString("name"));
								op.setUserName(rs.getString("username"));
								op.setId(rs.getInt("id"));
								return op;
							}

						});
		if(list.size()>0){
			operator.copy(list.get(0));
			operator.setPermissions(jdbcTemplate
					.query("select * from operatorpermission op,permission p where op.permissionID=p.id and op.operatorID=?",
							new Object[] { operator.getId() },
							new RowMapper<Permission>() {

								public Permission mapRow(ResultSet rs,
										int rowNum) throws SQLException {
									Permission permission = new Permission();
									permission.setDescription(rs
											.getString("description"));
									permission.setPermissionID(rs
											.getInt("op.permissionID"));
									return permission;
								}

						}));
		}
		return list.size()>0;
	}

	public boolean isUserNameExist(String userName) {
		int count = jdbcTemplate.queryForInt("select count(*) from operator where username=?", new Object[]{userName});
		return count==1;
	}
}
